package com.yeban.jdbc.preparestatement;

import com.yeban.jdbc.entity.Girl;
import com.yeban.jdbc.utils.JDBCUtils;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.time.LocalDate;
import java.time.LocalDateTime;
import java.time.format.DateTimeFormatter;
import java.util.ArrayList;
import java.util.List;

/**
 * @Desc xxxDao 与数据层交换
 * @Author yeban
 * @Date 2025/11/17
 */
public class GirlDao {

    // 需求1：通过姓名模糊查询女生列表
    public List<Girl> selectOneByLikeName(String query) {
        List<Girl> list = new ArrayList<>();
        // 1.获取连接对象
        Connection conn = JDBCUtils.getConn();
        PreparedStatement ppst = null;
        ResultSet rs = null;

        try {
            // 2.预编译SQL语句，获取语句对象
            String sql = "select * from girl where name like concat('%',?,'%')";
            ppst = conn.prepareStatement(sql);
            // 3.给?占位符传值
            ppst.setString(1, query);
            // 4.执行查询操作，获取结果集
            rs = ppst.executeQuery();
            // 5.处理结果集
            while(rs.next()) {
                Girl girl = new Girl();
                // 获取id - String类型
                girl.setId(rs.getInt("id"));
                // 获取name - String类型
                girl.setName(rs.getString("name"));
                // 获取sex - String类型
                girl.setSex(rs.getString("sex"));
                // 获取borndate - LocalDate类型
                String borndateStr = rs.getString("borndate");
                LocalDate borndate = LocalDate.parse(borndateStr, DateTimeFormatter.ofPattern("yyyy-MM-dd HH:mm:ss"));
                girl.setBorndate(borndate);
                // 获取phone - String类型
                girl.setPhone(rs.getString("phone"));
                // 获取boyfriend_id - Int类型
                girl.setBoyfriendId(rs.getInt("boyfriend_id"));
                list.add(girl);
            }
        } catch (SQLException e) {
            throw new RuntimeException(e);
        } finally {
            // 6. 关闭资源
            JDBCUtils.close(ppst, conn, rs);
        }
        return list;
    }

    // 需求2：插入女生信息（+ 获取主键自增的值）
    public int insertOne(Girl girl) {
        // 1.获取连接对象
        Connection conn = JDBCUtils.getConn();
        PreparedStatement ppst = null;  // 声明预编译SQL对象
        // 2.预编译SQL，获取语句对象
        try {
            String sql = "insert into girl values(NULL,?,DEFAULT,now(),?,NULL,?)";
            // TODD 第一个参数：SQL语句   第二个参数：获取的自增主键
            ppst = conn.prepareStatement(sql, PreparedStatement.RETURN_GENERATED_KEYS);
            // 3.给?占位符传值
            ppst.setString(1, girl.getName());
            ppst.setString(2, girl.getPhone());
            ppst.setInt(3, girl.getBoyfriendId());
            // 4.执行增删改操作
            int i = ppst.executeUpdate();
            // 获取自增主键的值
            ResultSet rs = ppst.getGeneratedKeys();
            while(rs.next()) {
                int id = rs.getInt(1);
                System.out.println("获取自增ID的值 = " + id);
            }
            return i;
        } catch (SQLException e) {
            throw new RuntimeException(e);
        } finally {
            JDBCUtils.close(ppst, conn, null);
        }
    }

    /*// 需求3：根据手机号更新女生信息
    public Girl updateByPhone(Girl girl, String phoneNum) {
        // 1.获取连接对象
        Connection conn = JDBCUtils.getConn();
        PreparedStatement ppst = null;
        // 2.预编译SQL，获取语句对象
        try {
            String sql = "update girl set name=?, sex=?, borndate=?, phone=?, boyfriend_id=? where phone = ?";
            ppst = conn.prepareStatement(sql);
            // 3.给?占位符传值
            ppst.setString(1, girl.getName());
            ppst.setString(2, girl.getSex());
            // 取String类型
            LocalDate borndate = girl.getBorndate();
            String borndateStr = borndate.format(DateTimeFormatter.ofPattern("yyyy-MM-dd HH:mm:ss"));
            ppst.setString(3, borndateStr);
            ppst.setString(4, girl.getPhone());
            ppst.setInt(5, girl.getBoyfriendId());
            ppst.setString(6, phoneNum);
            // 4.执行增删改操作
            int i = ppst.executeUpdate();
            if(i == 0) {
                return null;
            }
            System.out.println("已更新条数：" + i);
            ppst.close();

            // 查询对应的手机号所有信息
            String selectSql = "select * from girl where phone = ?";
            ppst = conn.prepareStatement(selectSql);
            ppst.setString(1, phoneNum);
            ResultSet rs = ppst.executeQuery();

            Girl newGirl = null;
            while(rs.next()) {
                newGirl = new Girl();
                // 获取id - String类型
                newGirl.setId(rs.getInt("id"));
                // 获取name - String类型
                newGirl.setName(rs.getString("name"));
                // 获取sex - String类型
                newGirl.setSex(rs.getString("sex"));
                // 获取borndate - LocalDate类型
                String borndateStr1 = rs.getString("borndate");
                LocalDate borndate1 = LocalDate.parse(borndateStr, DateTimeFormatter.ofPattern("yyyy-MM-dd HH:mm:ss"));
                newGirl.setBorndate(borndate);
                // 获取phone - String类型
                newGirl.setPhone(rs.getString("phone"));
                // 获取boyfriend_id - Int类型
                newGirl.setBoyfriendId(rs.getInt("boyfriend_id"));
            }
            return newGirl;
        } catch (SQLException e) {
            throw new RuntimeException(e);
        } finally {
            JDBCUtils.close(ppst, conn, null);
        }
    }*/

    // 需求3：根据手机号更新女生信息
    /*public int updateOne(Girl girl) {
        Connection conn = null;
        PreparedStatement ppst = null;
        try {
            // 1.获取数据库连接
            conn = JDBCUtils.getConn();

            // 2.预编译UPDATE语句（动态拼接需要更新的字段）
            StringBuilder sql = new StringBuilder("UPDATE girl SET ");
            List<Object> params = new ArrayList<>();
            boolean hasUpdateField = false;

            // 动态添加需要更新的字段（根据newGirl中非空的属性）
            if (newGirl.getId() != null) {
                sql.append("id = ?, ");
                params.add(newGirl.getId());
                hasUpdateField = true;
            }
            if (newGirl.getName() != null) {
                sql.append("name = ?, ");
                params.add(newGirl.getName());
                hasUpdateField = true;
            }
            if (newGirl.getSex() != null) {
                sql.append("sex = ?, ");
                params.add(newGirl.getSex());
                hasUpdateField = true;
            }
            if (newGirl.getBorndate() != null) {
                sql.append("borndate = ?, ");
                // 转换LocalDate为数据库存储的字符串格式（与查询时保持一致）
                String bornDateStr = newGirl.getBorndate()
                        .format(DateTimeFormatter.ofPattern("yyyy-MM-dd HH:mm:ss"));
                params.add(bornDateStr);
                hasUpdateField = true;
            }
            if (newGirl.getPhone() != null) {
                sql.append("phone = ?, ");
                params.add(newGirl.getPhone());
                hasUpdateField = true;
            }
            if (newGirl.getBoyfriendId() != null) {
                sql.append("boyfriend_id = ?, ");
                params.add(newGirl.getBoyfriendId());
                hasUpdateField = true;
            }

            // 若没有需要更新的字段，直接返回0
            if (!hasUpdateField) {
                return 0;
            }

            // 移除SQL末尾多余的逗号和空格，拼接WHERE条件（根据手机号更新）
            sql.delete(sql.length() - 2, sql.length());
            sql.append(" WHERE phone = ?");
            params.add(phone); // 最后添加查询条件的手机号

            // 3.设置参数并执行更新
            ppst = conn.prepareStatement(sql.toString());
            for (int i = 0; i < params.size(); i++) {
                ppst.setObject(i + 1, params.get(i)); // 参数索引从1开始
            }

            // 4.执行更新并返回影响的行数
            return ppst.executeUpdate();

        } catch (SQLException e) {
            throw new RuntimeException("根据手机号更新女生信息失败", e);
        } finally {
            // 5.关闭资源（无论成功失败都要关闭）
            JDBCUtils.close(ppst, conn, null);
        }
    }*/

    // 需求3：根据手机号更新女生信息
    public Girl updateByPhone(Girl girl, String oldPhone) {

        // 1、获取连接对象
        Connection conn = JDBCUtils.getConn();
        PreparedStatement ppst = null;

        try {
            // 2、预编译SQL语句，创建语句对象
            String sql = "update girl set name=?, sex=?, borndate=?, phone=?, boyfriend_id=? where phone = ?";
            ppst = conn.prepareStatement(sql);

            // name
            ppst.setString(1, girl.getName());
            // sex
            ppst.setString(2, girl.getSex());
            /*// borndate
            String borndateStr = girl.getBorndate().format(DateTimeFormatter.ofPattern("yyyy-MM-dd HH:mm:ss"));
            ppst.setString(3, borndateStr);*/
            // borndate
            DateTimeFormatter fm = DateTimeFormatter.ofPattern("yyyy-MM-dd HH:mm:ss");
            String borndateStr = girl.getBorndate().format(fm);
            ppst.setString(3, borndateStr);

            // phone
            ppst.setString(4, girl.getPhone());
            // boyfriend_id
            ppst.setInt(5, girl.getBoyfriendId());
            // oldPhone
            ppst.setString(6, oldPhone);

            int rows = ppst.executeUpdate();
            if (rows == 0) {
                return null;   // 根据旧手机号没有找到数据
            }
            ppst.close();

            // 手机号是否修改
            String finalPhone = girl.getPhone();  // 使用更新后的手机号查询

            // 2.查询最新数据
            String selectSql = "select * from girl where phone = ?";
            ppst = conn.prepareStatement(selectSql);
            ppst.setString(1, finalPhone);

            ResultSet rs = ppst.executeQuery();

            Girl newGirl = null;
            if (rs.next()) {
                newGirl = new Girl();
                newGirl.setId(rs.getInt("id"));
                newGirl.setName(rs.getString("name"));
                newGirl.setSex(rs.getString("sex"));

                String bdStr = rs.getString("borndate");
                LocalDate bd = LocalDate.parse(bdStr, DateTimeFormatter.ofPattern("yyyy-MM-dd HH:mm:ss"));
                newGirl.setBorndate(bd);
                /*DateTimeFormatter fm = DateTimeFormatter.ofPattern("yyyy-MM-dd HH:mm:ss");
                LocalDate bd = LocalDate.parse(bdStr, fm);
                newGirl.setBorndate(bd);*/

                newGirl.setPhone(rs.getString("phone"));
                newGirl.setBoyfriendId(rs.getInt("boyfriend_id"));
            }

            return newGirl;

        } catch (SQLException e) {
            throw new RuntimeException(e);
        } finally {
            JDBCUtils.close(ppst, conn, null);
        }
    }

    /*// 需求4：根据ID删除女生信息
    public void deleteById(int id) {
        // 1.创建连接对象
        Connection conn = JDBCUtils.getConn();
        PreparedStatement ppst = null;
        try {
            // 2.预编译SQL
            String sql = "delete from girl where id = ?";
            ppst = conn.prepareStatement(sql);
            // 3.给?占位符传值
            ppst.setInt(1, id);
            // 4.执行增删改语句
            int i = ppst.executeUpdate();
            System.out.println(i>1 ? "删除成功" : "删除失败");
        } catch (SQLException e) {
            throw new RuntimeException(e);
        } finally {
            JDBCUtils.close(ppst, conn, null);
        }
    }*/



    // 需求4：根据ID删除女生信息
    public void deleteById(int id) {

        Connection conn = JDBCUtils.getConn();
        PreparedStatement ppst = null;

        try {
            String sql = "delete from girl where id = ?";
            ppst = conn.prepareStatement(sql);
            ppst.setInt(1, id);

            int rows = ppst.executeUpdate();
            System.out.println(rows == 1 ? "删除成功" : "删除失败：ID不存在");

        } catch (SQLException e) {
            throw new RuntimeException(e);
        } finally {
            JDBCUtils.close(ppst, conn, null);
        }
    }

}
